package data;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.LinkedList;
import java.util.List;
import logic.Product;
import logic.ShoppingKart;
import logic.User;

public class JDBCProductDAO implements IProductDAO {
	
	Connection connection;

	//Tirso
	public void deleteProductsFromCatalogue(Connection conn, List<String> lp){
		PreparedStatement stmt = null;
		String sql = "UPDATE products SET sell=1 WHERE (id = ?) ";
		try{
			stmt = conn.prepareStatement(sql);
			for(String i:lp){
				stmt.setString(1, i);
				stmt.executeUpdate();
			}
		}catch (SQLException e) {
            System.out.println("Message: DeleteProductsFromCatalogue: " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("ErrorCode: " + e.getErrorCode());
        } finally {
            try {
                if (stmt != null) {
                    stmt.close();
                }
            }catch (SQLException e) {
            }
        }
    }

	
	public void addProduct(int idProduct) {
		// TODO Auto-generated method stub
	}
	

    public List<Product> selectNotActive() {
        Connection conn = ConnectionManager.getInstance().checkOut();

        PreparedStatement stmt = null;
        List<Product> searchResults = new LinkedList<Product>();
        ResultSet result = null;

        try {

            String sql = "SELECT * FROM products WHERE active = 0";
            stmt = conn.prepareStatement(sql); //conn es una conexion valida
            stmt.executeQuery();
            result = stmt.executeQuery();

            while (result.next()) {
                Product temp = new Product();
                temp.setId(result.getString("id"));
                temp.setCategory(result.getString("category"));
                temp.setName(result.getString("name"));
                temp.setDescription(result.getString("description"));
                temp.setPrice(result.getFloat("price"));
                temp.setIddonor(result.getString("iddonor"));
                temp.setIdbuyer(result.getString("idbuyer"));
                temp.setIddestination(result.getString("iddestination"));
                temp.setRegdate(result.getString("regdate"));
                temp.setActive(result.getBoolean("active"));
                searchResults.add(temp);
            }
        } catch (SQLException e) {
            System.out.println("Message: selectNotActive: " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("ErrorCode: " + e.getErrorCode());
        } finally {
            ConnectionManager.getInstance().checkIn(conn);
            try {
                if (result != null)
                    result.close();
                if (stmt != null)
                    stmt.close();
            } catch (SQLException e) {
            }

        }
        return searchResults;
    }

	//Tirso
	public String selectDestinationFromProduct(Connection c, String idProduct) {
		PreparedStatement stmt = null;
		String sql = "SELECT iddestination FROM products WHERE id = ?";
		String idDestination = null;
		ResultSet result = null;
		try{
			stmt = c.prepareStatement(sql);
			stmt.setString(1, idProduct);
			result = stmt.executeQuery();
			result.next();
			idDestination = result.getString("iddestination");
        } catch (SQLException e) {
            System.out.println("Message: selectDestinationFromProduct: " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("ErrorCode: " + e.getErrorCode());
        } finally {
            try {
                if (stmt != null)
                    stmt.close();
            } catch (SQLException e) {
            }
        }
        return idDestination;
	}
	
    //Tirso
	public float selectPrice(Connection c, String idProduct) {
		PreparedStatement stmt = null;
		String sql = "SELECT price FROM products WHERE id = ?";
		float price = 0;
		ResultSet result = null;
		try{
			stmt = c.prepareStatement(sql);
			stmt.setString(1, idProduct);
			result = stmt.executeQuery();
			result.next();
			price = result.getFloat("price");
        } catch (SQLException e) {
            System.out.println("Message: selectPrice: " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("ErrorCode: " + e.getErrorCode());
        } finally {
            try {
            	if(result != null)
            		result.close();
                if (stmt != null)
                    stmt.close();
            } catch (SQLException e) {
            }
        }
        return price;
	}

	//Tirso
	public void insertDestinationInProduct(Connection c, String idProduct, String idDestination) {
		PreparedStatement stmt = null;
		String sql = "UPDATE products SET iddestination = ? WHERE id = ?";
		try{
			stmt = c.prepareStatement(sql);
			stmt.setString(1, idDestination);
			stmt.setString(2, idProduct);
			stmt.executeUpdate();
        } catch (SQLException e) {
            System.out.println("Message: insertDestInProduct: " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("ErrorCode: " + e.getErrorCode());
        } finally {
            try {
                if (stmt != null)
                    stmt.close();
            } catch (SQLException e) {
            }
        }		
	}

	//Tirso
	public void insertBuyerInProduct(Connection con, String idProduct, String idBuyer) {
		PreparedStatement stmt = null;
		String sql = "UPDATE products SET idbuyer = ? WHERE id = ?";
		try{
			stmt = con.prepareStatement(sql);
			stmt.setString(1, idBuyer);
			stmt.setString(2, idProduct);
			stmt.executeUpdate();
        } catch (SQLException e) {
            System.out.println("Message: InsertBuyerInProduct: " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("ErrorCode: " + e.getErrorCode());
        } finally {
            try {
                if (stmt != null)
                    stmt.close();
            } catch (SQLException e) {
            }
        }		
	}

	
	public String selectDestinationFromProduct(String idProduct) {
		Connection con = ConnectionManager.getInstance().checkOut();
		PreparedStatement stmt = null;
		String sql = "SELECT iddestination FROM products WHERE (id = ?)";
		String idDestination = "";
		ResultSet result = null;
		try{
			stmt = con.prepareStatement(sql);
			stmt.setString(1, idProduct);
			stmt.executeQuery();
			result = stmt.executeQuery();
			idDestination = result.getString("iddestination");
        } catch (SQLException e) {
            System.out.println("Message: " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("ErrorCode: " + e.getErrorCode());
        } finally {
        	ConnectionManager.getInstance().checkIn(con);
            try {
                if (stmt != null)
                    stmt.close();
            } catch (SQLException e) {
            }
        }
        return idDestination;
	}
    
	
	//FRAN
    public List<Product> selectSellProducts(Connection conn, User u) {
    	PreparedStatement stmt = null;
        List<Product> searchResults = new LinkedList<Product>();
        ResultSet result = null;

        try {
            String sql = "SELECT * FROM products WHERE sell = true AND idbuyer = ? GROUP BY category ORDER BY count(*) DESC";
            stmt = conn.prepareStatement(sql);
            stmt.setString(1, u.getId());
            stmt.executeQuery();
            result = stmt.executeQuery();

            while (result.next()) {
                Product temp = new Product();
                temp.setId(result.getString("id"));
                temp.setCategory(result.getString("category"));
                temp.setName(result.getString("name"));
                temp.setDescription(result.getString("description"));
                temp.setPrice(result.getFloat("price"));
                temp.setIddonor(result.getString("iddonor"));
                temp.setIdbuyer(result.getString("idbuyer"));
                temp.setIddestination(result.getString("iddestination"));
                temp.setRegdate(result.getString("regdate"));
                temp.setActive(result.getBoolean("active"));
                temp.setSell(result.getBoolean("sell"));
                searchResults.add(temp);
            }
        } catch (SQLException e) {
            System.out.println("Message: Select sell products: " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("ErrorCode: " + e.getErrorCode());
        } finally {
            try {
                if (result != null)
                    result.close();
                if (stmt != null)
                    stmt.close();
            } catch (SQLException e) {
            }

        }
        return searchResults;
    }
    
    public String mostDestinyProfitsByUser(Connection conn, User u) {
    	PreparedStatement stmt = null;
        String searchResults = "";
        ResultSet result = null;

        try {
            String sql = "SELECT * FROM products WHERE sell = 1 AND iddonor = ? GROUP BY iddestination ORDER BY count(*) DESC";
            stmt = conn.prepareStatement(sql);
            stmt.setString(1, u.getId());
            stmt.executeQuery();
            result = stmt.executeQuery();

            while (result.next()) {
                searchResults = result.getString("iddestination");
            }
        } catch (SQLException e) {
            System.out.println("Message: mostDestinyProfitsByUser: " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("ErrorCode: " + e.getErrorCode());
        } finally {
            try {
                if (result != null)
                    result.close();
                if (stmt != null)
                    stmt.close();
            } catch (SQLException e) {
            }

        }
        return searchResults;
    }
    
    public List<Product> selectProductsByCategory(Connection conn, String cat) {
    	PreparedStatement stmt = null;
        List<Product> searchResults = new LinkedList<Product>();
        ResultSet result = null;

        try {
            String sql = "SELECT * FROM products WHERE active = true AND sell = false AND category = ? ORDER BY RAND() LIMIT 3 ";
            stmt = conn.prepareStatement(sql);
            stmt.setString(1, cat);
            stmt.executeQuery();
            result = stmt.executeQuery();

            while (result.next()) {
                Product temp = new Product();
                temp.setId(result.getString("id"));
                temp.setCategory(result.getString("category"));
                temp.setName(result.getString("name"));
                temp.setDescription(result.getString("description"));
                temp.setPrice(result.getFloat("price"));
                temp.setIddonor(result.getString("iddonor"));
                temp.setIdbuyer(result.getString("idbuyer"));
                temp.setIddestination(result.getString("iddestination"));
                temp.setRegdate(result.getString("regdate"));
                temp.setActive(result.getBoolean("active"));
                temp.setSell(result.getBoolean("sell"));
                searchResults.add(temp);
            }
        } catch (SQLException e) {
            System.out.println("Message: selectProductsByCategory: " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("ErrorCode: " + e.getErrorCode());
        } finally {
            try {
                if (result != null)
                    result.close();
                if (stmt != null)
                    stmt.close();
            } catch (SQLException e) {
            }

        }
        return searchResults;
    }
    
    public List<Product> selectProductByDestination(Connection conn, String dest) {
    	PreparedStatement stmt = null;
        List<Product> searchResults = new LinkedList<Product>();
        ResultSet result = null;

        try {
            String sql = "SELECT * FROM products WHERE active = true AND sell = false AND iddestination = ? ORDER BY RAND() LIMIT 5 ";
            stmt = conn.prepareStatement(sql);
            stmt.setString(1, dest);
            stmt.executeQuery();
            result = stmt.executeQuery();

            while (result.next()) {
                Product temp = new Product();
                temp.setId(result.getString("id"));
                temp.setCategory(result.getString("category"));
                temp.setName(result.getString("name"));
                temp.setDescription(result.getString("description"));
                temp.setPrice(result.getFloat("price"));
                temp.setIddonor(result.getString("iddonor"));
                temp.setIdbuyer(result.getString("idbuyer"));
                temp.setIddestination(result.getString("iddestination"));
                temp.setRegdate(result.getString("regdate"));
                temp.setActive(result.getBoolean("active"));
                temp.setSell(result.getBoolean("sell"));
                searchResults.add(temp);
            }
        } catch (SQLException e) {
            System.out.println("Message: SelectProductsByDest: " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("ErrorCode: " + e.getErrorCode());
        } finally {
            try {
                if (result != null)
                    result.close();
                if (stmt != null)
                    stmt.close();
            } catch (SQLException e) {
            }

        }
        return searchResults;
    }
    
    public List<Product> selectLastProduct(Connection conn, Integer n) {
    	PreparedStatement stmt = null;
        List<Product> searchResults = new LinkedList<Product>();
        ResultSet result = null;

        try {
            String sql = "SELECT * FROM products WHERE active = 1 AND sell = false ORDER BY id DESC LIMIT "+n;
            stmt = conn.prepareStatement(sql);
            stmt.executeQuery();
            result = stmt.executeQuery();

            while (result.next()) {
                Product temp = new Product();
                temp.setId(result.getString("id"));
                temp.setCategory(result.getString("category"));
                temp.setName(result.getString("name"));
                temp.setDescription(result.getString("description"));
                temp.setPrice(result.getFloat("price"));
                temp.setIddonor(result.getString("iddonor"));
                temp.setIdbuyer(result.getString("idbuyer"));
                temp.setIddestination(result.getString("iddestination"));
                temp.setRegdate(result.getString("regdate"));
                temp.setActive(result.getBoolean("active"));
                temp.setSell(result.getBoolean("sell"));
                searchResults.add(temp);
            }
        } catch (SQLException e) {
            System.out.println("Message: SelectLastProducts: " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("ErrorCode: " + e.getErrorCode());
        } finally {
            try {
                if (result != null)
                    result.close();
                if (stmt != null)
                    stmt.close();
            } catch (SQLException e) {
            }

        }
        return searchResults;
    }
    
    public List<Product> selectRandomProduct(Connection conn, Integer n) {
    	PreparedStatement stmt = null;
        List<Product> searchResults = new LinkedList<Product>();
        ResultSet result = null;

        try {
            String sql = "SELECT * FROM products WHERE active = 1 AND sell = false ORDER BY RAND() DESC LIMIT "+n;
            stmt = conn.prepareStatement(sql);
            stmt.executeQuery();
            result = stmt.executeQuery();

            while (result.next()) {
                Product temp = new Product();
                temp.setId(result.getString("id"));
                temp.setCategory(result.getString("category"));
                temp.setName(result.getString("name"));
                temp.setDescription(result.getString("description"));
                temp.setPrice(result.getFloat("price"));
                temp.setIddonor(result.getString("iddonor"));
                temp.setIdbuyer(result.getString("idbuyer"));
                temp.setIddestination(result.getString("iddestination"));
                temp.setRegdate(result.getString("regdate"));
                temp.setActive(result.getBoolean("active"));
                temp.setSell(result.getBoolean("sell"));
                searchResults.add(temp);
            }
        } catch (SQLException e) {
            System.out.println("Message: SelectRandomProduct: " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("ErrorCode: " + e.getErrorCode());
        } finally {
            try {
                if (result != null)
                    result.close();
                if (stmt != null)
                    stmt.close();
            } catch (SQLException e) {
            }

        }
        return searchResults;
    }



	public Product selectProduct(String id) {
		Connection con = ConnectionManager.getInstance().checkOut();
		PreparedStatement stmt = null;
        ResultSet result = null;
        Product p = new Product();
        String sql = "SELECT * FROM products WHERE (id = ?) ";

        try {
            
            stmt = con.prepareStatement(sql);
            stmt.setString(1, id);
            stmt.executeQuery();            
            result = stmt.executeQuery();

            while(result.next()){
            	p.setId(id);
            	p.setCategory(result.getString("category"));
            	p.setName(result.getString("name"));
            	p.setDescription(result.getString("description"));
            	p.setPrice(result.getFloat("price"));
            	p.setIddonor(result.getString("iddonor"));
            	p.setIddestination(result.getString("iddestination"));
            	p.setRegdate(result.getString("regdate"));
            	p.setActive(result.getBoolean("active"));
            	p.setSell(result.getBoolean("sell"));
            	p.setRatio(result.getDouble("ratio"));
            }
        } catch (SQLException e) {
            System.out.println("Message: SelectProduct(id): " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("ErrorCode: " + e.getErrorCode());
        } finally {
        	ConnectionManager.getInstance().checkIn(con);
            try {
                if (result != null)
                    result.close();
                if (stmt != null)
                    stmt.close();
            } catch (SQLException e) {
            }

        }
        return p;
		
	}
	
	/** 05/06/ Nando
     * Devuelve el dinero recaudado por todos los productos que ha donado el usuario
     * @param conn
     * @param idUser
     * @return
     */
    public float dineroRecaudado(Connection conn, int idUser){
    	PreparedStatement stmt = null;
        ResultSet result = null;
        float dinero = 0;

        try {
            String sql = "SELECT sum(sellprice) AS suma FROM products WHERE userid = ? ";
            stmt = conn.prepareStatement(sql);
            stmt.setLong(1, idUser);
            stmt.executeQuery();
            result = stmt.executeQuery();
            
            if(result.next()) {
            	dinero = result.getInt("suma");
            }
            
        } catch (SQLException e) {
            System.out.println("Message: " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("ErrorCode: " + e.getErrorCode());
        } finally {
            ConnectionManager.getInstance().checkIn(conn);
            try {
                if (result != null)
                    result.close();
                if (stmt != null)
                    stmt.close();
            } catch (SQLException e) {
            }

        }
        return dinero;
    }
    
    /**
     * Nando 
     * @param conn
     * @param idUser
     * @return
     */
    public int cantidadDonados(Connection conn, String idUser){
    	PreparedStatement stmt = null;
        ResultSet result = null;
        int cantidad = 0;

        try {
            String sql = "SELECT count(distinct(id)) AS cant FROM products WHERE iddonnor = ? ";
            stmt = conn.prepareStatement(sql);
            stmt.setString(1, idUser);
            stmt.executeQuery();
            result = stmt.executeQuery();
            
            if(result.next()) {
            	cantidad = result.getInt("cant");
            }
            
        } catch (SQLException e) {
            System.out.println("Message: " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("ErrorCode: " + e.getErrorCode());
        } finally {
            ConnectionManager.getInstance().checkIn(conn);
            try {
                if (result != null)
                    result.close();
                if (stmt != null)
                    stmt.close();
            } catch (SQLException e) {
            }

        }
        return cantidad;
    }
    /**Nando
     * 
     * @param idUser
     * @return
     */
    
    public List<Product> selectProdbyUser(String idUser){
    	Connection conn = ConnectionManager.getInstance().checkOut();

        PreparedStatement stmt = null;
        List<Product> searchResults = new LinkedList<Product>();
        ResultSet result = null;

        try {

            String sql = "SELECT * FROM products WHERE iddonner = ?";
            stmt = conn.prepareStatement(sql); //conn es una conexion valida
            stmt.setString(1, idUser);
            stmt.executeQuery();
            result = stmt.executeQuery();

            while (result.next()) {
                Product temp = new Product();
                temp.setId(result.getString("id"));
                temp.setCategory(result.getString("category"));
                temp.setName(result.getString("name"));
                temp.setDescription(result.getString("description"));
                temp.setPrice(result.getFloat("price"));
                temp.setIddonor(result.getString("iddonor"));
                temp.setIdbuyer(result.getString("idbuyer"));
                temp.setIddestination(result.getString("iddestination"));
                temp.setRegdate(result.getString("regdate"));
                temp.setActive(result.getBoolean("active"));
                searchResults.add(temp);
            }
        } catch (SQLException e) {
            System.out.println("Message: SelectProdByUser: " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("ErrorCode: " + e.getErrorCode());
        } finally {
            ConnectionManager.getInstance().checkIn(conn);
            try {
                if (result != null)
                    result.close();
                if (stmt != null)
                    stmt.close();
            } catch (SQLException e) {
            }

        }
        return searchResults;
    }
    


	public List<Product> selectActiveProducts() {
        Connection conn = ConnectionManager.getInstance().checkOut();

        PreparedStatement stmt = null;
        List<Product> searchResults = new LinkedList<Product>();
        ResultSet result = null;

        try {

            String sql = "SELECT * FROM products WHERE active = 1";
            stmt = conn.prepareStatement(sql); 
            stmt.executeQuery();
            result = stmt.executeQuery();

            while (result.next()) {
                Product temp = new Product();
                temp.setId(result.getString("id"));
                temp.setCategory(result.getString("category"));
                temp.setName(result.getString("name"));
                temp.setDescription(result.getString("description"));
                temp.setPrice(result.getFloat("price"));
                temp.setIddonor(result.getString("iddonor"));
                temp.setIdbuyer(result.getString("idbuyer"));
                temp.setIddestination(result.getString("iddestination"));
                temp.setRegdate(result.getString("regdate"));
                temp.setActive(result.getBoolean("active"));
                searchResults.add(temp);
            }
        } catch (SQLException e) {
            System.out.println("Message: SelectActiveProduct: " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("ErrorCode: " + e.getErrorCode());
        } finally {
            ConnectionManager.getInstance().checkIn(conn);
            try {
                if (result != null)
                    result.close();
                if (stmt != null)
                    stmt.close();
            } catch (SQLException e) {
            }

        }
        return searchResults;
    }
	/**
     * Nando
     * cuenta los productos donados por idUser que hayan sido comprados 
     * @param conn
     * @param idUser
     * @return
     */
    public int amountDonatedPurchased(String idUser){
    	Connection conn = ConnectionManager.getInstance().checkOut();
    	PreparedStatement stmt = null;
        ResultSet result = null;
        int amount = 0;

        try {
            String sql = "SELECT count(distinct(id)) AS cant FROM products WHERE iddonor = ? AND sell = 1";
            stmt = conn.prepareStatement(sql);
            stmt.setString(1, idUser);
            stmt.executeQuery();
            result = stmt.executeQuery();
            
            if(result.next()) {
            	
            	amount = result.getInt("cant");
            	System.out.println(amount);
            }
            
        } catch (SQLException e) {
            System.out.println("Message: amountDonatedPurchased: " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("ErrorCode: " + e.getErrorCode());
        } finally {
        	ConnectionManager.getInstance().checkIn(conn);
            try {
                if (result != null)
                    result.close();
                if (stmt != null)
                    stmt.close();
            } catch (SQLException e) {
            }

        }
        return amount;
    }
    /** 
     * TODO Hacer la insercion correctamente
     */
    public void insert(Product p){
    	Connection conn = ConnectionManager.getInstance().checkOut();
    	PreparedStatement stmt = null;
    	String sql = "INSERT INTO products (id, category, name, description, price, iddonor, iddestination, sell, active, ratio) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
        try {
           	stmt = conn.prepareStatement(sql);
            stmt.setString(1, p.getId());
            stmt.setString(2, p.getCategory());
            stmt.setString(3, p.getName());
            stmt.setString(4, p.getDescription());
            stmt.setFloat(5, p.getPrice());
            stmt.setString(6, p.getIddonor());
            stmt.setString(7, p.getIddestination());
            stmt.setBoolean(8, p.isSell());
            stmt.setBoolean(9, p.isActive());
            stmt.setDouble(10, p.getRatio());
            stmt.executeUpdate();

    	     } catch (SQLException e) {
    	            System.out.println("Message: insert: " + e.getMessage());
    	            System.out.println("SQLState: " + e.getSQLState());
    	            System.out.println("ErrorCode: " + e.getErrorCode());
    	        } finally {
    	        	ConnectionManager.getInstance().checkIn(conn);
                try {
                    if (stmt != null) {
                        stmt.close();
                    }
                } catch (SQLException e) {
                	}
    	      }
        }        
        
    
    
    
    public float moneyRaised(String idUser){
    	Connection conn = ConnectionManager.getInstance().checkOut();
    	PreparedStatement stmt = null;
        ResultSet result = null;
        float money = 0;

        try {
            String sql = "SELECT sum(price) AS suma FROM products WHERE iddonor = ? ";
            stmt = conn.prepareStatement(sql);
            stmt.setString(1, idUser);
            stmt.executeQuery();
            result = stmt.executeQuery();
            
            if(result.next()) {
            	money = result.getInt("suma");
            }
            
        } catch (SQLException e) {
            System.out.println("Message: moneyRaised: " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("ErrorCode: " + e.getErrorCode());
        } finally {
            ConnectionManager.getInstance().checkIn(conn);
            try {
                if (result != null)
                    result.close();
                if (stmt != null)
                    stmt.close();
            } catch (SQLException e) {
            }

        }
        return money;
    }
    
    /**
     * Nando 
     * @param conn
     * @param idUser
     * @return
     */
    public int amountDonate(String idUser){
    	Connection conn = ConnectionManager.getInstance().checkOut();
    	PreparedStatement stmt = null;
        ResultSet result = null;
        int amount = 0;

        try {
            String sql = "SELECT count(distinct(id)) AS cant FROM products WHERE iddonor = ? ";
            stmt = conn.prepareStatement(sql);
            stmt.setString(1, idUser);
            stmt.executeQuery();
            result = stmt.executeQuery();
            
            if(result.next()) {
            	amount = result.getInt("cant");
            }
            
        } catch (SQLException e) {
            System.out.println("Message: amountDonate: " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("ErrorCode: " + e.getErrorCode());
        } finally {
            ConnectionManager.getInstance().checkIn(conn);
            try {
                if (result != null)
                    result.close();
                if (stmt != null)
                    stmt.close();
            } catch (SQLException e) {
            }

        }
        return amount;
    }
    
    public List<Product> selectNProductNotActive(Integer n) {
		Connection conn = ConnectionManager.getInstance().checkOut();

    	PreparedStatement stmt = null;
        List<Product> searchResults = new LinkedList<Product>();
        ResultSet result = null;

        try {
            String sql = "SELECT * FROM products WHERE active = 0 ORDER BY ratio DESC LIMIT "+n;
            stmt = conn.prepareStatement(sql);
            stmt.executeQuery();
            result = stmt.executeQuery();
            if(!result.next()){
            	return null;
            }

            while (result.next()) {
                Product temp = new Product();
                temp.setId(result.getString("id"));
                temp.setCategory(result.getString("category"));
                temp.setName(result.getString("name"));
                temp.setDescription(result.getString("description"));
                temp.setPrice(result.getFloat("price"));
                temp.setIddonor(result.getString("iddonor"));
                temp.setIdbuyer(result.getString("idbuyer"));
                temp.setIddestination(result.getString("iddestination"));
                temp.setRegdate(result.getString("regdate"));
                temp.setActive(result.getBoolean("active"));
                temp.setSell(result.getBoolean("sell"));
                temp.setRatio(result.getDouble("ratio"));
                searchResults.add(temp);
            }
        } catch (SQLException e) {
            System.out.println("Message: " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("ErrorCode: " + e.getErrorCode());
        } finally {
        	ConnectionManager.getInstance().checkIn(conn);
            try {
                if (result != null)
                    result.close();
                if (stmt != null)
                    stmt.close();
            } catch (SQLException e) {
            }

        }
        return searchResults;
    }

    /**
     * TODO Nando Eliminar producto
     */
	
	public void delete(String idProduct) {
		Connection conn = ConnectionManager.getInstance().checkOut();
		String sql = "DELETE FROM products WHERE id = ? ";
        PreparedStatement stmt = null;

        try {
            stmt = conn.prepareStatement(sql);
            stmt.setString(1, idProduct);
            stmt.executeUpdate();
        } catch (SQLException e) {
            System.out.println("Message: " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("ErrorCode: " + e.getErrorCode());
        } finally {
        	ConnectionManager.getInstance().checkIn(conn);
            try {
                if (stmt != null)
                    stmt.close();
            } catch (SQLException e) {
            }
        }
        
	}
	
	public void update(Product p) {
		Connection conn = ConnectionManager.getInstance().checkOut();
		PreparedStatement stmt = null;
		String sql = "UPDATE products SET active = ? WHERE id = ?";
        try {
            stmt = conn.prepareStatement(sql);
            /**stmt.setString(1, p.getCategory());
            stmt.setString(2, p.getName());
            stmt.setString(3, p.getDescription());
            stmt.setFloat(4, p.getPrice());
            stmt.setString(5, p.getIddonor());
            stmt.setString(6, p.getIddestination());
            stmt.setBoolean(7, p.isSell());
            stmt.setBoolean(8, p.isActive());
            stmt.setDouble(9, p.getRatio());**/
            stmt.setBoolean(1, true);
            stmt.setString(2, p.getId());
            
        
            stmt.executeUpdate();

	        } catch (SQLException e) {
	            System.out.println("Message: insertPurchase: " + e.getMessage());
	            System.out.println("SQLState: " + e.getSQLState());
	            System.out.println("ErrorCode: " + e.getErrorCode());
	        } finally {
	        	ConnectionManager.getInstance().checkIn(conn);
            try {
            	
                if (stmt != null) {
                    stmt.close();
                }
            } catch (SQLException e) {
            }
	    }
	}

}
